"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 传入引号包裹的session id，查询成功，会隐式转换
Create At   : 2023/12/1
Owner       : opentestcase022
Description :
    1.建表
    2.新建session1，创建预备语句
    3.获取session1的session_id
    4.传入session1的session_id使用PG_PREPARED_STATEMENT函数查询
    5.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功，查询结果中有stmt1
    5.执行成功
History     :
"""

import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement8(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.t_name = 't_prepare_08'
        self.prepare_name = 'stmt1'
        self.prepare_sql = f"prepare {self.prepare_name} as " \
            f"insert into {self.t_name} values(1, 'openGauss');"

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:新建session1，创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(f'{self.prepare_sql}'
                                   f'select pg_sleep(10);', ))
        session1.setDaemon(True)
        session1.start()
        time.sleep(1)

        text = 'step3:获取session1的session_id; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f"SELECT sa.sessionid AS sid, 0::integer AS serial#, " \
            f"ad.rolname AS username, sa.backend_start as backend_start " \
            f"FROM pg_stat_get_activity(NULL) AS sa " \
            f"LEFT JOIN pg_authid ad " \
            f"ON(sa.usesysid=ad.oid) " \
            f"WHERE sa.application_name <>'JobScheduler' " \
            f"order by 4 desc limit 1 offset 1;"
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        rows, _ = self.common.parse_sql_query_res(res)

        text = 'step3:新建session2，传入session1的session_id' \
               '使用PG_PREPARED_STATEMENT函数查询; ' \
               'expect:执行成功，查询结果中没有stmt1'
        self.log.info(f'-----{text}-----')
        query_sql = f"select * from pg_prepared_statement('{rows[0][0]}') " \
            f"order by 1, 2;"
        self.log.info(query_sql)
        res = self.pri_sh.execut_db_sql(f'{query_sql}')
        self.log.info(res)
        self.assertIn(self.prepare_name, res, f'执行失败：{text}')
        self.assertIn(self.prepare_sql, res, f'执行失败：{text}')

        text = '获取session1结果'
        self.log.info(f'-----{text}-----')
        session1.join(30)
        res = session1.get_result()
        self.log.info(res)
        self.assertIn('PREPARE', res, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text = 'step4:清理环境; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
